package com.vecspace.database.model;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;

import com.vecspace.constant.DBType;
import com.vecspace.data.model.FilterColumn;
import com.vecspace.jsmodel.SqlParam;

/** 
 * @version: 1.0
 * @author: wanghua
 * @date 2021-7-3
 */
public class QuerySQL implements Cloneable {

	private String queryColumns;

	private String tables;

	private String where;

	private String orderby;
	private boolean orderSiblings ;

	/**
	 WITH RECURSIVE cte_name AS
	 (
	 SELECT ... -- return initial row set
	 UNION ALL / UNION DISTINCT
	 SELECT ... -- return additional row sets
	 )
	 SELECT * FROM cte_name;
	 */

	private String cteSql;//CTE递归

	private final List<QuerySqlGroup> groups = new ArrayList<>();
	/**
	 * sql的参数值
	 */
	private Object[] sqlParameterValues;

	public QuerySQL() {

	}

	/**
	 * @param queryColumns 查询字段
	 * @param tables       表名，单个表名，或者多个表的组合子句 比如 TaleA a left join TableB b on a.pid = b.id
	 * @param where        查询条件，比如 a.name = 'zhangsan'
	 * @param orderby      排序字段， 比如  age desc, name asc
	 */
	public QuerySQL(String queryColumns, String tables, String where, String orderby) {
		super();
		this.queryColumns = queryColumns;
		this.tables = tables;
		this.where = where;
		this.orderby = orderby;
	}

	/**
	 * @param queryColumns     查询字段
	 * @param tables           表名，单个表名，或者多个表的组合子句 比如 TaleA a left join TableB b on a.pid = b.id
	 * @param where            查询条件，比如 a.name = 'zhangsan'
	 * @param groupColumns     分组字段，用于 group by 的字段，比如 sex
	 * @param aggregateColumns 聚合字段，比如 count(name), sum(salary), avg(salary)
	 * @param orderby          排序字段， 比如  age desc, name asc
	 */
	public QuerySQL(String queryColumns, String tables, String where, String groupColumns, String aggregateColumns, String orderby) {
		super();
		this.queryColumns = queryColumns;
		this.tables = tables;
		this.where = where;
		this.orderby = orderby;
		if(StringUtils.isNotBlank(groupColumns) && StringUtils.isNotBlank(aggregateColumns)) {
			this.addQueryGroup(null, "", groupColumns, aggregateColumns);
		}
	}

	/**
	 * 参数是否正确设置了
	 *
	 * @return
	 */
	public boolean valid() {
		return StringUtils.isNotBlank(tables);
	}

	/**
	 * 是否有聚合查询
	 *
	 * @return
	 * @author wanghua
	 */
	public boolean hasAggregate() {
		return groups.size() > 0;
	}

	/**
	 * 获取不分页的简单查询语句 “select ... from ... where ... order by ...”
	 *
	 * @return
	 * @author wanghua
	 */
	public String getQuerySqlText() {
		StringBuilder sql = new StringBuilder(256);
		if(StringUtils.isNotBlank(cteSql)){
			sql.append(cteSql);
		}
		sql.append("select ").append(queryColumns).append(" from ").append(tables);
		if (StringUtils.isNotBlank(where)) {
			sql.append(" where ").append(where);
		}
		if (StringUtils.isNotBlank(orderby)) {
			if(orderSiblings) {
				sql.append(" order siblings by ");
			}else {
				sql.append(" order by ");
			}
			sql.append(orderby);
		}
		return sql.toString();
	}

	/**
	 * 获取聚合查询语句（Group By）
	 *
	 * @return
	 * @author wanghua
	 */
	public String getAggregateSqlText(int i) {
		QuerySqlGroup group = groups.get(i);
		String groupColumns = group.getGroupColumns();
		String aggregateColumns = group.getAggregateColumns();
		StringBuilder sql = new StringBuilder(256);
		if(StringUtils.isNotBlank(cteSql)){
			sql.append(cteSql);
		}
		if (StringUtils.isNotBlank(groupColumns)) {
			sql.append("select ").append(groupColumns).append(",").append(aggregateColumns).append(" from ").append(tables);
		} else {
			sql.append("select ").append(aggregateColumns).append(" from ").append(tables);
		}
		if (StringUtils.isNotBlank(where)) {
			sql.append(" where ").append(where);
		}
		
		if (StringUtils.isNotBlank(groupColumns)) {
			sql.append(" group by ").append(groupColumns);
		}

		if (StringUtils.isNotBlank(groupColumns)) {
			sql.append(" order by ").append(groupColumns);
		}
		return sql.toString();
	}

	/**
	 * 获取查询总记录数的sql
	 *
	 * @return
	 * @author wanghua
	 */
	public String getTotalRowsSqlText() {
		StringBuilder sql = new StringBuilder(256);
		if(StringUtils.isNotBlank(cteSql)){
			sql.append(cteSql);
		}
		sql.append("select ").append("count(*)").append(" from ").append(tables);
		if(StringUtils.isNotBlank(where)) {
			sql.append(" where ").append(where);
		}
		
		return sql.toString();
	}

	/**
	 * 分页查询语句
	 *
	 * @param pageIndex 查询第几页数数据，base 1（从1开始）
	 * @param pageSize  每页的数据条数
	 * @return
	 * @author wanghua
	 */
	public String getPageSqlText(int pageIndex, int pageSize) {
		String pageSql = "";
		if (DBType.isOracleOfCurrent()) {
			pageSql = getOraclePageSqlText(pageIndex, pageSize);
		} else if (DBType.isPostgreSQLOfCurrent()) {
			pageSql = getPostgreSQLPageSqlText(pageIndex, pageSize);
		} else if (DBType.isMySQLOfCurrent()) {
			pageSql = getMySqlPageSqlText(pageIndex, pageSize);
		} else if (DBType.isMSSQLOfCurrent()) {
			pageSql = getMSSQLPageSqlText(pageIndex, pageSize);
		}
		return pageSql;
	}

	/**
	 * 分页查询语句
	 *
	 * @param pageIndex 查询第几页 base 1，从1开始
	 * @param pageSize  每页几条记录
	 * @return
	 * @author wanghua
	 */
	private String getMySqlPageSqlText(int pageIndex, int pageSize) {
		//select * from student limit(curPage-1)*pageSize,pageSize;
		String sql = getQuerySqlText();
		int start = (pageIndex - 1) * pageSize;
		String limitSql = " limit " + start + " , " + pageSize;
		sql = sql + limitSql;
		return sql;
	}

	/**
	 * 分页查询语句
	 *
	 * @param pageIndex 查询第几页 base 1，从1开始
	 * @param pageSize  每页几条记录
	 * @return
	 * @author wanghua
	 */
	private String getOraclePageSqlText(int pageIndex, int pageSize) {
		int startrow = pageIndex * pageSize + 1;
		int endrow = startrow + pageSize;
		String sql = getQuerySqlText();

		String querySql = "select tmp_tab0_.*, ROWNUM as row_num_ from (" + sql + ") tmp_tab0_ WHERE ROWNUM < " + endrow;
		String pageSql = "SELECT * FROM (" + querySql + ") tmp_tab2_ WHERE tmp_tab2_.row_num_ >= " + startrow;

		return pageSql;
	}

	private String getPostgreSQLPageSqlText(int pageIndex, int pageSize) {
		//select * from persons limit  A  offset  B;  A就是你需要多少行；B就是查询的起点位置。A B是bigint类型的值
		//SELECT select_list FROM table_expression [ ORDER BY ... ]	[ LIMIT { number | ALL } ] [ OFFSET number ]
		String sql = getQuerySqlText();
		int start = (pageIndex - 1) * pageSize;
		String limitSql = " limit " + pageSize + " offset " + start;
		sql = sql + limitSql;
		return sql;
	}

	/**
	 * 分页查询语句 (MSSQL server 2012版本及以上)
	 *
	 * @param pageIndex 查询第几页 base 1，从1开始
	 * @param pageSize  每页几条记录
	 * @return
	 * @author wanghua
	 */
	private String getMSSQLPageSqlText(int pageIndex, int pageSize) {
		/* offset A rows ,将前A条记录舍去，fetch next B rows only ，向后在读取B条数据。
		 * select * from student order by sno offset ((@pageIndex-1)*@pageSize) rows fetch next @pageSize rows only;
		 select * from student order by sno offset 10 rows fetch next 10 rows only 
		*/
		int start = (pageIndex - 1) * pageSize;
		String sql = getQuerySqlText();
		String limitSql = " offset " + start + " rows fetch next " + pageSize + " rows only ";
		String pageSql = sql + limitSql;

		return pageSql;
	}

	public int getGroupCount(){
		return groups.size();
	}

	public String getGroupName(int i){
		QuerySqlGroup group = groups.get(i);
		return group.getGroupName();
	}
	public String getGroupLabel(int i){
		QuerySqlGroup group = groups.get(i);
		return group.getGroupLabel();
	}
	
	public void setQueryColumns(String queryColumns) {
		this.queryColumns = queryColumns;
	}

	/**添加分组查询
	 *
	 * @param groupName 分组名称
	 * @param groupLabel 分组名称（中文名）
	 * @param groupColumns 分组字段，不能为空
	 * @param aggregateColumns 聚合字段，不能为空
	 */
	public void addQueryGroup(String groupName, String groupLabel, String groupColumns, String aggregateColumns) {
		if(StringUtils.isBlank(aggregateColumns))return;
		groups.add(new QuerySqlGroup(groupName, groupLabel, groupColumns, aggregateColumns));
	}

	/**
	 * 表名，单个表名，或者多个表的组合子句 比如 TaleA a left join TableB b on a.pid = b.id
	 *
	 * @param tables
	 * @author: wanghua
	 */
	public void setTables(String tables) {
		this.tables = tables;
	}

	/**
	 * @param where
	 * @author: wanghua
	 */
	public void setWhere(String where) {
		this.where = where;
	}

	/**
	 * 排序字段， 比如  age desc, name asc
	 *
	 * @param orderby
	 */
	public void setOrderby(String orderby) {
		this.orderby = orderby;
	}

	public void setOrderby(String sortColumn, String sortOrder) {
		String orderby = "";
		if (StringUtils.isNotBlank(sortColumn)) {
			orderby = sortColumn;
			if (StringUtils.isNotBlank(sortOrder)) {
				orderby = sortColumn + " " + sortOrder;
			}
		}
		this.orderby = orderby;
	}

	/**
	 * 排序字段， 比如  age desc, name asc
	 *
	 * @return
	 */
	public String getOrderby() {
		return orderby;
	}

	public String getQueryColumns() {
		return queryColumns;
	}

	/**
	 * 表名，单个表名，或者多个表的组合子句 比如 TaleA a left join TableB b on a.pid = b.id
	 *
	 * @return
	 * @author: wanghua
	 */
	public String getTables() {
		return tables;
	}

	public String getWhere() {
		return where;
	}

	/**
	 * sql的参数值
	 */
	public Object[] getSqlParameterValues() {
		return sqlParameterValues;
	}

	/**
	 * sql的参数值
	 * 在addQueryFilter之前设置的sqlParameterValues，将和addQueryFilter添加的filters的参数合并，并且在filters参数之前。
	 */
	public void setSqlParameterValues(Object[] sqlParameterValues) {
		this.sqlParameterValues = sqlParameterValues;
	}

	/**
	 * 在已经构造QuerySQL的基础上，增加查询过滤条件。
	 * <p>
	 * 需要注意：已经构造的QuerySQL中的where子句如果带有 ? 条件占位符，则一定要先setSqlParameterValues，然后再addQueryFilter。
	 *
	 * @param filters
	 * @throws Exception
	 * @author wanghua
	 * @reviewer
	 * @createTime 2021年9月30日 上午11:04:56
	 */
	public void addQueryFilter(List<FilterColumn> filters) throws Exception {
		if (filters == null || filters.size() == 0) return;
		Object[] params = null;// 查询参数对应的数值数组
		//把查询条件列表转为查询条件sql语句和查询参数
		SqlParam sqlParam = FilterColumn.toSqlParam(filters);
		String whereClause = sqlParam.getSql();//查询条件sql 
		List<Object> paramList = sqlParam.getParameter();//查询参数List [,,]
		if (paramList != null) {
			params = paramList.toArray();//查询参数转为数组 [,,]
		}
		String fullWhere = this.where;
		if (fullWhere == null) fullWhere = "";

		if (!StringUtils.isEmpty(whereClause)) {
			if (fullWhere.contains("[and]")) {
				fullWhere = fullWhere.replace("[and]", " and " + whereClause);
			} else if (fullWhere.contains("[where]")) {
				fullWhere = fullWhere.replace("[where]", whereClause);
			} else {
				if (StringUtils.isNotBlank(fullWhere)) {
					fullWhere += " and " + whereClause;
				} else {
					fullWhere = whereClause;
				}
			}
		} else {
			if (fullWhere.contains("[and]")) {
				fullWhere = fullWhere.replace("[and]", "");
			} else if (fullWhere.contains("[where]")) {
				fullWhere = fullWhere.replace("[where]", "");
			}
		}

		this.where = fullWhere;

		//查询条件的值
		if (params != null && params.length > 0) {
			if (sqlParameterValues == null) {
				sqlParameterValues = params;
			} else {
				sqlParameterValues = ArrayUtils.addAll(sqlParameterValues, params);
			}
		}
	}

	public String getCteSql() {
		return cteSql;
	}

	public void setCteSql(String cteSql) {
		this.cteSql = cteSql;
	}

	public boolean isOrderSiblings() {
		return orderSiblings;
	}

	public void setOrderSiblings(boolean orderSiblings) {
		this.orderSiblings = orderSiblings;
	}

	public List<QuerySqlGroup> getGroups() {
		return groups;
	}

	@Override
	public QuerySQL clone() {
		try {
			return (QuerySQL) super.clone();
		} catch (CloneNotSupportedException e) {
			e.printStackTrace();
			return null;
		}
	}
}
